﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public static class ProductDataAccess
    {

        private const string ConnectionString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static Collection<long> GetProductCompleteInfo(long productId)
        {
            Collection<long> products = new Collection<long>();
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ID FROM PRODUCT WHERE ID=@id ";
                command.Parameters.AddWithValue("ID", productId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    long product = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting product list", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return products;
        }

        public static void UpdateProducts(Collection<Product> products)
        {
            if (products != null)
            {
                 SqlConnection connection = null;
                 try
                 {
                     connection = new SqlConnection(ConnectionString);
                     foreach (Product product in products)
                     {
                         connection.Open();
                         SqlCommand updateQuantity = connection.CreateCommand();
                         updateQuantity.CommandText = "Update Product Set Quantity=(Quantity-@Quantity) Where ID=@ProdId";
                         updateQuantity.Parameters.AddWithValue("Quantity", product.Quantity);
                         updateQuantity.Parameters.AddWithValue("ProdId", product.ProductId);
                         int result = updateQuantity.ExecuteNonQuery();
                         if (result <= 0)
                         {
                             throw new DataAccessException("Error completing an order");
                         }
                         connection.Close();

                     }
                     connection.Open();
                     SqlCommand command = connection.CreateCommand();
                     command.CommandText = "UPDATE PRODUCT SET Status=@Status WHERE Quantity=@zero";
                     command.Parameters.AddWithValue("Status", "Sold");
                     command.Parameters.AddWithValue("zero", 0);
                     command.ExecuteNonQuery();
                 }
                 catch (SqlException ex)
                 {
                     throw new DataAccessException("Error completing an order", ex);
                 }
                 finally
                 {
                     if (connection != null)
                     {
                         connection.Close();
                     }
                     SqlConnection.ClearAllPools();
                 }  
            }
        }

        public static Product GetProductById(long productId)
        {
            Product product = new Product();
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM PRODUCT WHERE ID=@Id";
                command.Parameters.AddWithValue("Id", productId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    product.ProductId = productId;
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    product.HasImages = images != 0;
                    product.Status = reader["Status"].ToString();
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return product;
        }

        public static DataSet GetProductsBySeller(long sellerId)
        {
            DataSet products = null;
            SqlConnection connection = null;
            SqlDataAdapter adapter = null;
            try
            {
                products = new DataSet();
                products.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM PRODUCT WHERE SellerID=@SellerId and Status=@status";
                command.Parameters.AddWithValue("SellerId", sellerId);
                command.Parameters.AddWithValue("status", "OnSale");
                adapter = new SqlDataAdapter(command);
                adapter.Fill(products);
                connection.Open();
                command.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting Product List", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (products != null)
                {
                    products.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
            return products;
        }

        public static Collection<Product> GetProductsBySeller(long sellerId, bool noDataTable)
        {
            if (!noDataTable)
            {
                return null;
            }
            Collection<Product> products = new Collection<Product>();
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ID,Name,Description,Price,Currency,Images,Category,PosDate,Status,Quantity,IsShown FROM PRODUCT WHERE SellerID=@SellerId and (Status=@status1 or Status=@status2)";
                command.Parameters.AddWithValue("SellerId", sellerId);
                command.Parameters.AddWithValue("status1", "OnSale");
                command.Parameters.AddWithValue("status2", "OnAuction");
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    product.Status = reader["Status"].ToString();
                    int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    product.HasImages = images != 0;
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    product.IsShown = string.IsNullOrEmpty(reader["IsShown"].ToString()) ? false : bool.Parse(reader["IsShown"].ToString());
                    if (product.Status == "OnSale" || product.Status=="OnAuction")
                        products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return products;
        }

        public static Product ModifyProduct(Product product)
        {
            if (product != null)
            {
                SqlConnection connection = null;
                connection = new SqlConnection(ConnectionString);
                try
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "UPDATE PRODUCT SET Name=@ProductName,Description=@Description,Price=@Price,Currency=@Currency,Images=@hasImages,Category=@Category,PosDate=@PostDate,Status=@status,Quantity=@quantity,IsShown=@isShown WHERE ID=@productId";
                    command.Parameters.AddWithValue("ProductName", product.Name);
                    command.Parameters.AddWithValue("Description", product.Description);
                    command.Parameters.AddWithValue("Price", product.Price);
                    command.Parameters.AddWithValue("Currency", product.Currency);
                    command.Parameters.AddWithValue("hasImages", product.HasImages);
                    command.Parameters.AddWithValue("Category", product.Category);
                    command.Parameters.AddWithValue("PostDate", product.Postdate);
                    command.Parameters.AddWithValue("productId", product.ProductId);
                    command.Parameters.AddWithValue("status", product.Status);
                    command.Parameters.AddWithValue("quantity", product.Quantity);
                    command.Parameters.AddWithValue("isShown", product.IsShown);
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error modifying a product");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error modifying a product", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }

            return product;
        }

        public static string GetProductDescription(string id)
        {
            string product = null;
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT Description FROM PRODUCT WHERE ID=@Id";
                command.Parameters.AddWithValue("Id", id);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    product = reader["Description"].ToString();
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a description", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return product;
        }


        public static Product InsertProduct(Product product)
        {
            if (product != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "INSERT INTO PRODUCT(Name,Description,Price,Currency,Images,Category,Status,PosDate,SellerID,Quantity,IsShown) VALUES(@ProductName,@Description,@Price,@Currency,@hasImages,@Category,@Status,@PostDate,@SellerID,@Quantity,@isShown)";
                    command.Parameters.AddWithValue("ProductName", product.Name);
                    command.Parameters.AddWithValue("Description", product.Description);
                    command.Parameters.AddWithValue("Price", product.Price);
                    command.Parameters.AddWithValue("Currency", product.Currency);
                    command.Parameters.AddWithValue("hasImages", product.HasImages);
                    command.Parameters.AddWithValue("Category", product.Category);
                    command.Parameters.AddWithValue("Status", product.Status);
                    command.Parameters.AddWithValue("PostDate", product.Postdate);
                    command.Parameters.AddWithValue("SellerID", product.SellerId);
                    command.Parameters.AddWithValue("Quantity", product.Quantity);
                    command.Parameters.AddWithValue("isShown", product.IsShown);
                    SqlCommand getID = connection.CreateCommand();
                    getID.CommandText = "SELECT @@Identity";

                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting a product");
                    }
                    product.ProductId = Convert.ToInt64(getID.ExecuteScalar(), CultureInfo.CurrentCulture);
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting a product", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }

            return product;
        }
        


        public static object ViewProductsOnSale()
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {
                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ID,Name,Category,Price FROM PRODUCT where Status='OnSale'";
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "PRODUCT");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["PRODUCT"].DefaultView;
                return dataView;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting products", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Product> ViewRecommendedProductsOnSale(long id,string category)
        {
            SqlConnection connection = null;
            SqlDataReader reader = null;
            SqlCommand command = null;
            Collection<Product> products = new Collection<Product>();
            try
            {
                connection = new SqlConnection(ConnectionString);
                command = connection.CreateCommand();
                command.CommandText = "SELECT ID,Name,Category,Price,Currency FROM PRODUCT where Status='OnSale' and ID!=@id and Category=@category";
                command.Parameters.AddWithValue("ID",id);
                command.Parameters.AddWithValue("Category",category);
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Name = reader["Name"].ToString();
                    product.ProductId = Convert.ToInt64(reader["ID"].ToString(),CultureInfo.CurrentCulture);
                    product.Category = reader["Category"].ToString();
                    product.Price = float.Parse(reader["Price"].ToString(),CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    products.Add(product);
                }
                return products;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting products", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<Product> ViewProductsDealsGifts()
        {
            SqlConnection connection = null;
            SqlDataReader reader = null;
            SqlCommand command = null;
            Collection<Product> products = new Collection<Product>();
            try
            {
                connection = new SqlConnection(ConnectionString);
                command = connection.CreateCommand();
                command.CommandText = "SELECT ID,Name,Category,Price,Currency FROM PRODUCT where Status='OnSale' and Category='Deals & Gifts'";//Deals & Gifts goes on Gift Card
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Name = reader["Name"].ToString();
                    product.ProductId = Convert.ToInt64(reader["ID"].ToString(),CultureInfo.CurrentCulture);
                    product.Category = reader["Category"].ToString();
                    product.Price = float.Parse(reader["Price"].ToString(),CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    products.Add(product);
                }
                return products;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting gifts products", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<Product> ViewAllProductsOnSale()
        {
            SqlConnection connection = null;
            SqlDataReader reader = null;
            SqlCommand command = null;
            Collection<Product> products = new Collection<Product>();
            try
            {
                connection = new SqlConnection(ConnectionString);
                command = connection.CreateCommand();
                command.CommandText = "select p.Category,p.Currency,p.Description,p.ID,p.Images,p.Name,p.PosDate,p.Price,p.Quantity,p.SellerID,p.Status from Product p where p.Status=@status1 and p.Category!='Deals & Gifts'	Union select p2.Category,p2.Currency,p2.Description,p2.ID,p2.Images,p2.Name,p2.PosDate,p2.Price,p2.Quantity,p2.SellerID,p2.Status from Product p2 join  Auction a on a.ProductID=p2.ID	and a.Status=@status3 and p2.Category!='Deals & Gifts'";//Deals & Gifts goes on Gift Card
                command.Parameters.AddWithValue("status1", "OnSale");
                command.Parameters.AddWithValue("status3", "InProcess");
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Name = reader["Name"].ToString();
                    product.ProductId = Convert.ToInt64(reader["ID"].ToString(),CultureInfo.CurrentCulture);
                    product.Category = reader["Category"].ToString();
                    product.Price = float.Parse(reader["Price"].ToString(),CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    product.Quantity = short.Parse(reader["Quantity"].ToString(),CultureInfo.CurrentCulture);
                    products.Add(product);
                }
                return products;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting products", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static object DescriptionProduct(string id)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = null;
            SqlDataAdapter adapter = null;
            try
            {
                ds = new DataSet();
                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT Description,Price,Currency,Quantity FROM PRODUCT  where ID=@ID ";
                command.Parameters.AddWithValue("ID",id);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "PRODUCT");  
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["PRODUCT"].DefaultView;
                return dataView;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting products", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Product InsertProductRecommendation(Product product1, Product product2)
        {
            if (product1 != null && product2 != null)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "INSERT INTO PRODUCTRECOMMENDATION(ProductID,RecommendedProductID) VALUES(@ProductID,@RecommendedProductID)";
                    command.Parameters.AddWithValue("ProductID", product1.ProductId);
                    command.Parameters.AddWithValue("RecommendedProductID", product2.ProductId);

                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting a Product Recommendation");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting a Product Recommendation", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }

            return product1;
        }
        
        public static DataTable GetProductsByUser(User user)
        {
            if (user != null)
            {
                SqlConnection connection = null;
                DataSet dataSet = new DataSet();
                SqlDataAdapter dataAdapter = null;
                try
                {
                    dataSet.Locale = CultureInfo.CurrentCulture;
                    connection = new SqlConnection(ConnectionString);
                    SqlCommand command = connection.CreateCommand();
                    //DataView dataView=null;
                    command.CommandText = "SELECT ID,Name,Price,Currency,Category,PosDate FROM Product WHERE Status=@status and SellerID=@id";
                    command.Parameters.AddWithValue("id", user.Id);
                    command.Parameters.AddWithValue("status", "OnSale");
                    connection.Open();
                    dataAdapter = new SqlDataAdapter(command);
                    dataAdapter.Fill(dataSet, "Product");
                    return dataSet.Tables["Product"];

                }
                catch (Exception ex)
                {
                    throw new DataAccessException("Error get products", ex);
                }
                finally
                {

                    if (connection != null)
                    {
                        connection.Close();
                    }
                    if (dataSet != null)
                    {
                        dataSet.Dispose();
                    }
                    if (dataAdapter != null)
                    {
                        dataAdapter.Dispose();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }

        public static Collection<Product> GetProductsByCategory(string category)
        {
            Collection<Product> productos = new Collection<Product>();
            if (category == null)
            {
                return null;
            }
            else
            {
                SqlConnection connection = null;
                SqlCommand command = null;
                SqlDataReader reader = null;
                try
                {
                    connection = new SqlConnection(ConnectionString);
                    command = connection.CreateCommand();
                    command.CommandText = "SELECT ID, Name,Category, Price, Description FROM Product WHERE Category=@category and Status=@status";
                    command.Parameters.AddWithValue("category", category);
                    command.Parameters.AddWithValue("status", "OnSale");
                    connection.Open();
                    reader = command.ExecuteReader();
                    if (!reader.HasRows)
                    {
                        return null;
                    }
                    while (reader.Read())
                    {
                        Product product = new Product();
                        product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                        product.Name = reader["Name"].ToString();
                        product.Category = reader["Category"].ToString();
                        product.Description = reader["Description"].ToString();
                        //product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                        product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                       // product.Currency = reader["Currency"].ToString();
                        //product.Status = reader["Status"].ToString();
                        //int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                        //product.HasImages = images != 0;
                        //product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                        //product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                        productos.Add(product);
                    }
                    reader.Close();
                }
                catch (Exception er)
                {
                    throw new DataAccessException("Error getting products by category", er);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
                return productos;
            }
        }

        public static Collection<Product> GetProductsAuctionByCategoryAndDate(string category, DateTime dateB, DateTime dateE)
        {
            Collection<Product> result = new Collection<Product>();
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlDataReader reader = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                command = connection.CreateCommand();
                command.CommandText = "select p.Category,p.Currency,p.Description,p.ID,p.Images,p.Name,p.PosDate,p.Price,p.Quantity,p.SellerID,p.Status from Product p where  p.Status=@status1 and p.PosDate<=@dateE and p.PosDate>=@dateB and p.Category=@category Union select p2.Category,p2.Currency,p2.Description,p2.ID,p2.Images,p2.Name,p2.PosDate,p2.Price,p2.Quantity,p2.SellerID,p2.Status from Product p2 join  Auction a on a.ProductID=p2.ID	and a.Status=@status3 and p2.PosDate<=@dateE and p2.PosDate>=@dateB and p2.Category=@category";
                command.Parameters.AddWithValue("category", category);
                command.Parameters.AddWithValue("status1", "OnSale");
                command.Parameters.AddWithValue("status2", "OnAuction");
                command.Parameters.AddWithValue("status3", "InProcess");
                command.Parameters.AddWithValue("dateB", dateB);
                command.Parameters.AddWithValue("dateE", dateE);
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return result;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    product.Status = reader["Status"].ToString();
                    //int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    //product.HasImages = images != 0;
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    result.Add(product);
                }
                reader.Close();
            }
            catch (Exception er)
            {
                throw new DataAccessException("Error getting products by dates and category", er);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return result;
        }


        public static Collection<Product> Products()
        {
            Collection<Product> result = new Collection<Product>();
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlDataReader reader = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                command = connection.CreateCommand();
                command.CommandText = "select p.Category,p.Currency,p.Description,p.ID,p.Images,p.Name,p.PosDate,p.Price,p.Quantity,p.SellerID,p.Status from Product p where p.Status=@status1 and p.Category!='Deals & Gifts'	Union select p2.Category,p2.Currency,p2.Description,p2.ID,p2.Images,p2.Name,p2.PosDate,p2.Price,p2.Quantity,p2.SellerID,p2.Status from Product p2 join  Auction a on a.ProductID=p2.ID	and a.Status=@status3 and p2.Category!='Deals & Gifts'";
                command.Parameters.AddWithValue("status1", "OnSale");
                command.Parameters.AddWithValue("status3", "InProcess");
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return result;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    product.Status = reader["Status"].ToString();
                    //int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    //product.HasImages = images != 0;
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    result.Add(product);
                }
                reader.Close();
            }
            catch (Exception er)
            {
                throw new DataAccessException("Error getting products", er);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return result;
        }


        public static Collection<Product> GetProductsAuctionByDate(DateTime dateB, DateTime dateE)
        {
            Collection<Product> result = new Collection<Product>();
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlDataReader reader = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                command = connection.CreateCommand();
                command.CommandText = "select p.Category,p.Currency,p.Description,p.ID,p.Images,p.Name,p.PosDate,p.Price,p.Quantity,p.SellerID,p.Status from Product p where  p.Status=@status1 and p.PosDate<=@dateE and p.PosDate>=@dateB and p.Category!='Deals & Gifts' Union select p2.Category,p2.Currency,p2.Description,p2.ID,p2.Images,p2.Name,p2.PosDate,p2.Price,p2.Quantity,p2.SellerID,p2.Status from Product p2 join  Auction a on a.ProductID=p2.ID	and a.Status=@status3 and p2.PosDate<=@dateE and p2.PosDate>=@dateB and p2.Category!='Deals & Gifts'";
                command.Parameters.AddWithValue("status1", "OnSale");
                command.Parameters.AddWithValue("status2", "OnAuction");
                command.Parameters.AddWithValue("status3", "InProcess");
                command.Parameters.AddWithValue("dateB", dateB);
                command.Parameters.AddWithValue("dateE", dateE);
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return result;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    product.Status = reader["Status"].ToString();
                    //int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    //product.HasImages = images != 0;
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    result.Add(product);
                }
                reader.Close();
            }
            catch (Exception er)
            {
                throw new DataAccessException("Error getting products by dates", er);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return result;
        }


        public static Product Unsubscribe(Product product)
        {
            if (product != null){                
                SqlConnection connection = null;
                connection = new SqlConnection(ConnectionString);
                try
                {
                    product.Status = "Down";
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "UPDATE PRODUCT SET Status=@Status WHERE ID=@productId";
                    command.Parameters.AddWithValue("productId", product.ProductId);
                    command.Parameters.AddWithValue("Status", product.Status);

                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error unsubscribing a product");
                    }
                   
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error unsubscribing a product", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
                
            }
            return product;
        
        }

        public static Collection<Product> GetProductsAuctionByCategory(string category)
        {
            Collection<Product> result = new Collection<Product>();
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlDataReader reader = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                command = connection.CreateCommand();
                command.CommandText = "select p.Category,p.Currency,p.Description,p.ID,p.Images,p.Name,p.PosDate,p.Price,p.Quantity,p.SellerID,p.Status from Product p where  p.Status=@status1 and p.Category=@category Union select p2.Category,p2.Currency,p2.Description,p2.ID,p2.Images,p2.Name,p2.PosDate,p2.Price,p2.Quantity,p2.SellerID,p2.Status from Product p2  join  Auction a on a.ProductID=p2.ID	and a.Status=@status3 and p2.Category=@category";
                command.Parameters.AddWithValue("category", category);
                command.Parameters.AddWithValue("status1", "OnSale");
                command.Parameters.AddWithValue("status3", "InProcess");
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return result;
                }
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    product.Status = reader["Status"].ToString();
                    //int images = int.Parse(reader["Images"].ToString(), CultureInfo.CurrentCulture);
                    //product.HasImages = images != 0;
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);
                    result.Add(product);
                }
                reader.Close();
            }
            catch (Exception er)
            {
                throw new DataAccessException("Error getting products by category", er);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return result;
        }

        public static object ViewProductQuantity(long productId)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {
                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnectionString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT Quantity FROM PRODUCT where ID=@id";
                command.Parameters.AddWithValue("ID",productId);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "PRODUCT");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["PRODUCT"].DefaultView;
                return dataView;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting products", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }
 
    }
}
